package handler.importremotedb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hn.comm.Tools;

public class ImportProverbToDB {

	public static void main(String[] args) throws SQLException,
			ClassNotFoundException {
		String fileName = "D:\\eclipse_workspace\\indigo\\shoutchinese-robot\\res\\谚语.txt";
		String content = Tools.readFile(fileName);
		String[] arraySpl = content.split("\r\n");
		List<Proverb> arrayPb = new ArrayList<Proverb>();
		System.out.println(arraySpl.length);
		for (int i = 0; i < arraySpl.length; i++) {
			Proverb pb = new Proverb();
			pb.setEnglish(arraySpl[i]);
			i++;
			pb.setChinese(arraySpl[i]);
			arrayPb.add(pb);
		}
		System.out.println(arrayPb.size());

		Connection connInsert = Tools.getConnection("com.mysql.jdbc.Driver",
				"jdbc:mysql://localhost:3306/sc_test", "root", "root");

		Connection connQuery = Tools.getConnection("com.mysql.jdbc.Driver",
				"jdbc:mysql://localhost:3306/sc_test", "root", "root");

		String sqlStr = "select count(1) as num from proverb p where p.english=";

		PreparedStatement ps = connInsert
				.prepareStatement("INSERT into proverb(chinese,english) values ( ?, ?)");

		for (int n = 0; n < arrayPb.size(); n++) {
			Proverb pb = arrayPb.get(n);
			String querySql = sqlStr;
			// 查询是否存在
			querySql += "\"" + pb.getEnglish() + "\"";
			try {
				Statement st = connQuery.createStatement();
				ResultSet rs = st.executeQuery(querySql);
				while (rs.next()) {
					int num = rs.getInt("num");
					if (num == 0) {
						ps.setString(1, pb.getChinese());
						ps.setString(2, pb.getEnglish());
						ps.executeUpdate();
					}

				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		// System.out.println(arraySpl[1]);
	}
}

class Proverb {
	private String chinese;
	private String english;

	public String getChinese() {
		return chinese;
	}

	public void setChinese(String chinese) {
		this.chinese = chinese;
	}

	public String getEnglish() {
		return english;
	}

	public void setEnglish(String english) {
		this.english = english;
	}

}
